package com.pic.mgt.dao.haishin;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Collections;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Vector;

import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.RowMapper;

import com.pic.mgt.dao.BasicDao;
import com.pic.mgt.jdbc.CachedPreparedStatemntJdbcOperations;
import com.pic.mgt.jdbc.CachedPreparedStatemntJdbcTemplate;
import com.pic.mgt.utils.IgxCollectionUtils;
import com.pic.mgt.utils.SqlScriptUtils;

public final class AppointedHaishinBusinessAsgmtActionDao
			extends BasicDao implements IAppointedHaishinBusinessAsgmtActionDao {
	
	private CachedPreparedStatemntJdbcOperations cachedPreparedStatemntJdbcOperations;
	
	
	// 資料表名稱
	private final String tableName = "appointed_haishin_business_asgmt_action";
	
	// insert basic 相關設定
	private final String insertBasicSqlScript;
	private final Vector<String> insertBasicFieldsVector;
	private final long insertBasicTimeout = 5 * 1000;
	
	private final String findHaishinBasicBusinessAsgmtActionInfoListByUnitNameWithOrderByCreatingTimeDESCSqlScript;
	private final String findHaishinBasicBusinessAsgmtActionInfoByAsgmtFilenameSqlScript;
	private final String findHaishinBasicBusinessAsgmtActionInfoByUnitNameAndTerminalIdSqlScript;
	private final String updateCreatingTimeByAsgmtFilenameSqlScript;
	
	// update last transaction time 相關設定
	private final String updateLastTxTimeByTxFilenameSqlScript;
	private final Vector<String> updateLastTxTimeByTxFilenameFieldsVector;
	
	public AppointedHaishinBusinessAsgmtActionDao() {
		insertBasicFieldsVector = new Vector<String>();
		updateLastTxTimeByTxFilenameFieldsVector = new Vector<String>();
		
		// 建立 Insert Basic 的 Sql Scritp
		insertBasicSqlScript = createInsertBasicSQLScript();
		
		// 建立 Update Last Transaction Time By Tranasction Filename 的 Sql Script
		updateLastTxTimeByTxFilenameSqlScript = createUpdateLastTxTimeByTxFilenameSqlScript();
		
		// 建立 update creating time by asgmt file name 的 Sql Script
		this.updateCreatingTimeByAsgmtFilenameSqlScript = createUpdateCreatingTimeByAsgmtFilenameSqlScript();
		
		// 建立 find haishin basic business asgmt action info by unit name and terminal id 的 Sql Script
		this.findHaishinBasicBusinessAsgmtActionInfoByUnitNameAndTerminalIdSqlScript = createFindHaishinBasicBusinessAsgmtActionInfoByUnitNameAndTerminalIdSqlScript();
		
		// 建立 find haishin basic business asgmt action info list by unit name with order by creating time desc 的 sql script
		this.findHaishinBasicBusinessAsgmtActionInfoListByUnitNameWithOrderByCreatingTimeDESCSqlScript =
				createFindHaishinBasicBusinessAsgmtActionInfoListByUnitNameWithOrderByCreatingTimeDESCSqlScript();
		
		// 建立 find haishin basic business asgmt action info by asgmt file name 的 sql script
		this.findHaishinBasicBusinessAsgmtActionInfoByAsgmtFilenameSqlScript = createFindHaishinBasicBusinessAsgmtActionInfoByAsgmtFilenameSqlScript();
	}
	
	private String createFindHaishinBasicBusinessAsgmtActionInfoByAsgmtFilenameSqlScript() {
		Vector<String> selectFieldsVector =
			new Vector<String>();

		selectFieldsVector.add(_terminalId);
		selectFieldsVector.add(_asgmtFilename);
		selectFieldsVector.add(_asgmtFilesize);
		selectFieldsVector.add(_lastTxTime);
		selectFieldsVector.add(_creatingTime);
		selectFieldsVector.add(_result);

		return SqlScriptUtils.createNamedParamQueryScriptWithSingleCondition(
				getTableName(), selectFieldsVector, _asgmtFilename);
	}
	
	private String createFindHaishinBasicBusinessAsgmtActionInfoByUnitNameAndTerminalIdSqlScript() {
		Vector<String> selectFieldsVector =
							new Vector<String>();

		selectFieldsVector.add(_terminalId);
		selectFieldsVector.add(_asgmtFilename);
		selectFieldsVector.add(_asgmtFilesize);
		selectFieldsVector.add(_lastTxTime);
		selectFieldsVector.add(_creatingTime);
		selectFieldsVector.add(_result);

		return SqlScriptUtils.createNamedParamQueryScriptWithMultiConditions(
				getTableName(), selectFieldsVector,
				new String[] {_unitName, "AND", _terminalId});
	}
	
	private String createFindHaishinBasicBusinessAsgmtActionInfoListByUnitNameWithOrderByCreatingTimeDESCSqlScript() {
		Vector<String> selectFieldsVector =
			new Vector<String>();

		selectFieldsVector.add(_terminalId);
		selectFieldsVector.add(_asgmtFilename);
		selectFieldsVector.add(_asgmtFilesize);
		selectFieldsVector.add(_lastTxTime);
		selectFieldsVector.add(_creatingTime);
		selectFieldsVector.add(_result);

		return SqlScriptUtils.createNamedParamQueryScriptWithSorting(
				getTableName(),
				selectFieldsVector,
				_unitName,
				new String[] {_creatingTime, "DESC"});
	}
	
	public String createInsertBasicSQLScript() {
		// 要新增的欄位
		insertBasicFieldsVector.add(IAppointedHaishinBusinessAsgmtActionDao._targetFile);
		insertBasicFieldsVector.add(IAppointedHaishinBusinessAsgmtActionDao._unitName);
		insertBasicFieldsVector.add(IAppointedHaishinBusinessAsgmtActionDao._terminalId);
		insertBasicFieldsVector.add(IAppointedHaishinBusinessAsgmtActionDao._asgmtFilename);
		insertBasicFieldsVector.add(IAppointedHaishinBusinessAsgmtActionDao._asgmtFilesize);
		insertBasicFieldsVector.add(IAppointedHaishinBusinessAsgmtActionDao._result);

		return SqlScriptUtils.createInsertScript(this.tableName,
												 insertBasicFieldsVector);
	}
	
	private String createUpdateCreatingTimeByAsgmtFilenameSqlScript() {
		Vector<String> updateFieldsVector = new Vector<String>();
		
		updateFieldsVector.add(_creatingTime);
		
		return SqlScriptUtils.createNamedParamUpdateScript(
										tableName,
										updateFieldsVector,
										_asgmtFilename);
	}
	
	private String createUpdateLastTxTimeByTxFilenameSqlScript() {
		updateLastTxTimeByTxFilenameFieldsVector.add(
						IAppointedHaishinBusinessAsgmtActionDao._lastTxTime);
		
		return
			SqlScriptUtils.createNamedParamUpdateScript(
										tableName,
										updateLastTxTimeByTxFilenameFieldsVector,
										IAppointedHaishinBusinessAsgmtActionDao._asgmtFilename);
	}

	@Override
	public int insertBasic(Map<String, Object> insertValuesMap)
			throws DataAccessException {
		if (null != insertValuesMap && !insertValuesMap.isEmpty()) {
			// 將 inserValuesMap 內的值做正確順序排序
			Object[] orderedFieldValues = 
				CachedPreparedStatemntJdbcTemplate.
					sortValuesByGivingFieldVector(insertValuesMap,
												  this.insertBasicFieldsVector);
			// 新增資料
			return
				this.cachedPreparedStatemntJdbcOperations.
								executeUpdate(this.insertBasicSqlScript,
											  orderedFieldValues);
		}
		else {
			return 0;
		}
	}
	
	@Override
	public int updateCreatingTimeByAsgmtFilename(Timestamp creatTime, String asgmtFilename)
			throws DataAccessException {
		if (null != creatTime && null != asgmtFilename) {
			Map<String, Object> updateValuesMap =
				IgxCollectionUtils.convertToMap(
						new Object[] {_creatingTime, creatTime,
									  "__ConditionValue", asgmtFilename});
		
		return
			super.namedParameterJdbcTemplate.update(
							this.updateCreatingTimeByAsgmtFilenameSqlScript,
							updateValuesMap);
		}
		else {
			return 0;
		}
	}

	@Override
	public int updateLastTxTimeByTxFilename(Timestamp lastTransactionTime,
			String txFilename) throws DataAccessException {
		if (null != lastTransactionTime && null != txFilename) {
			Map<String, Object> updateValuesMap =
				IgxCollectionUtils.convertToMap(
						new Object[] {AppointedHaishinBusinessAsgmtActionDao._lastTxTime, lastTransactionTime,
									  "__ConditionValue", txFilename});
			
			return
				super.namedParameterJdbcTemplate.update(this.updateLastTxTimeByTxFilenameSqlScript,
														updateValuesMap);
		}
		else {
			return 0;
		}
	}
	
	@Override
	public HaishinBusinessAsgmtActionInfoBean findHaishinBasicBusinessAsgmtActionInfoByUnitNameAndTerminalId(
			String unitName, String terminalId) throws DataAccessException {
		try {
			return
			super.namedParameterJdbcTemplate.queryForObject(
					this.findHaishinBasicBusinessAsgmtActionInfoByUnitNameAndTerminalIdSqlScript,
					IgxCollectionUtils.convertToMap(
							new Object[] {_unitName, unitName,
										  _terminalId, terminalId}),
					new RowMapper<HaishinBusinessAsgmtActionInfoBean>() {
						public HaishinBusinessAsgmtActionInfoBean mapRow(ResultSet rs, int rowNum) throws SQLException {
							HaishinBusinessAsgmtActionInfoBean haishinBusinessAsgmtActionInfoBean = new HaishinBusinessAsgmtActionInfoBean();
						
							haishinBusinessAsgmtActionInfoBean.setTerminalId(rs.getString(_terminalId));
							haishinBusinessAsgmtActionInfoBean.setAsgmtFilename(rs.getString(_asgmtFilename));
							haishinBusinessAsgmtActionInfoBean.setAsgmtFilesize(rs.getLong(_asgmtFilesize));
							haishinBusinessAsgmtActionInfoBean.setResult(rs.getString(_result));
							haishinBusinessAsgmtActionInfoBean.setCreatingTime(new Date(rs.getTimestamp(_creatingTime).getTime()));
							
							Timestamp latestTxTimestamp = rs.getTimestamp(_lastTxTime);
							if (null != latestTxTimestamp) {
								haishinBusinessAsgmtActionInfoBean.setLastTransactionTime(new Date(latestTxTimestamp.getTime()));
							}
							else {
								haishinBusinessAsgmtActionInfoBean.setLastTransactionTime(null);
							}
							
							return haishinBusinessAsgmtActionInfoBean;
						}
					}
				);
		} catch (EmptyResultDataAccessException e) {
			return null;
		} catch (DataAccessException e) {
			throw e;
		}
	}
	
	@Override
	public List<HaishinBusinessAsgmtActionInfoBean> findHaishinBasicBusinessAsgmtActionInfoListByUnitNameWithOrderByCreatingTimeDESC(
			String unitName) throws DataAccessException {
		return
		super.namedParameterJdbcTemplate.query(
				this.findHaishinBasicBusinessAsgmtActionInfoListByUnitNameWithOrderByCreatingTimeDESCSqlScript,
				IgxCollectionUtils.convertToMap(
						new Object[] {_unitName, unitName}),
			new RowMapper<HaishinBusinessAsgmtActionInfoBean>() {
				public HaishinBusinessAsgmtActionInfoBean mapRow(ResultSet rs, int rowNum) throws SQLException {
					HaishinBusinessAsgmtActionInfoBean haishinBusinessAsgmtActionInfoBean = new HaishinBusinessAsgmtActionInfoBean();						
					
					haishinBusinessAsgmtActionInfoBean.setTerminalId(rs.getString(_terminalId));
					haishinBusinessAsgmtActionInfoBean.setAsgmtFilename(rs.getString(_asgmtFilename));
					haishinBusinessAsgmtActionInfoBean.setAsgmtFilesize(rs.getLong(_asgmtFilesize));
					haishinBusinessAsgmtActionInfoBean.setResult(rs.getString(_result));
					haishinBusinessAsgmtActionInfoBean.setCreatingTime(new Date(rs.getTimestamp(_creatingTime).getTime()));
					
					Timestamp latestTxTimestamp = rs.getTimestamp(_lastTxTime);
					if (null != latestTxTimestamp) {
						haishinBusinessAsgmtActionInfoBean.setLastTransactionTime(new Date(latestTxTimestamp.getTime()));
					}
					else {
						haishinBusinessAsgmtActionInfoBean.setLastTransactionTime(null);
					}
					
					return haishinBusinessAsgmtActionInfoBean;
				}
			  });
	}
	
	@Override
	public HaishinBusinessAsgmtActionInfoBean findHaishinBasicBusinessAsgmtActionInfoByAsgmtFilename(
			String asgmtFilename) throws DataAccessException {
		try {
			return
			super.namedParameterJdbcTemplate.queryForObject(
					this.findHaishinBasicBusinessAsgmtActionInfoByAsgmtFilenameSqlScript,
					Collections.singletonMap((String)_asgmtFilename, (Object)asgmtFilename),
					new RowMapper<HaishinBusinessAsgmtActionInfoBean>() {
						public HaishinBusinessAsgmtActionInfoBean mapRow(ResultSet rs, int rowNum) throws SQLException {
							HaishinBusinessAsgmtActionInfoBean haishinBusinessAsgmtActionInfoBean = new HaishinBusinessAsgmtActionInfoBean();
						
							haishinBusinessAsgmtActionInfoBean.setTerminalId(rs.getString(_terminalId));
							haishinBusinessAsgmtActionInfoBean.setAsgmtFilename(rs.getString(_asgmtFilename));
							haishinBusinessAsgmtActionInfoBean.setAsgmtFilesize(rs.getLong(_asgmtFilesize));
							haishinBusinessAsgmtActionInfoBean.setResult(rs.getString(_result));
							haishinBusinessAsgmtActionInfoBean.setCreatingTime(new Date(rs.getTimestamp(_creatingTime).getTime()));
							
							Timestamp latestTxTimestamp = rs.getTimestamp(_lastTxTime);
							if (null != latestTxTimestamp) {
								haishinBusinessAsgmtActionInfoBean.setLastTransactionTime(new Date(latestTxTimestamp.getTime()));
							}
							else {
								haishinBusinessAsgmtActionInfoBean.setLastTransactionTime(null);
							}
							
							return haishinBusinessAsgmtActionInfoBean;
						}
					}
				);
		} catch (EmptyResultDataAccessException e) {
			return null;
		} catch (DataAccessException e) {
			throw e;
		}
	}
	
	@Override
	public String getTableName() {
		return this.tableName;
	}
	
	// --- getter and setter ---

	public void setCachedPreparedStatemntJdbcOperations(
			CachedPreparedStatemntJdbcOperations cachedPreparedStatemntJdbcOperations) {
		this.cachedPreparedStatemntJdbcOperations = cachedPreparedStatemntJdbcOperations;
		
		this.cachedPreparedStatemntJdbcOperations.addPreparedStatementScript(
						this.insertBasicSqlScript, this.insertBasicTimeout);
	}

}
